﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Common;
using System.Data;
using SYH.Config;

namespace SYH.User.DAL
{
    public class UserSimplyInfo
    {

        #region 增
        /// <summary>
        /// 新增会员
        /// </summary>
        /// <param name="Model"></param>
        /// <returns></returns>
        public int AddUser(Model.UserSimplyInfo Model)
        {
            SqlParameter[] parameters = { 
                                                new SqlParameter("@Name",Model.Name),
                                                new SqlParameter("@Password",Model.Password),
                                                new SqlParameter("@Email",Model.Email),
                                                new SqlParameter("@Nature",Model.Nature),
                                                new SqlParameter("@Ip",Model.Ip),
                                                new SqlParameter("@QQ",Model.QQ),
                                                new SqlParameter("@CompanyName",Model.CompanyName),
                                                new SqlParameter("@LinkMan",Model.LinkMan),
                                                new SqlParameter("@Mobile",Model.Mobile),
                                                new SqlParameter("@ProvinceId",Model.ProvinceId),
                                                new SqlParameter("@CityId",Model.CityId), 
                                                new SqlParameter("@AreaId",Model.AreaId),
                                                new SqlParameter("@Sex",Model.Sex),
                                                new SqlParameter("@IndustryId",Model.IndustryId)
                                            };
            return TypeParse.ObjectToInt(SqlHelper.ExecuteScalar(WebConfig.SqlConnString,
                CommandType.StoredProcedure,
                "User_Registration",
                parameters), 0);
        }
        #endregion

        #region  查
        /// <summary>
        /// GetModel
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Model.UserSimplyInfo GetModel(int Id)
        {
            SqlDataReader sdr = SqlHelper.ExecuteReader(Config.WebConfig.SqlConnString,
                CommandType.StoredProcedure,
                "User_SimplyInfo_GetModel",
                new SqlParameter("@Id", Id));
            if (sdr.HasRows)
            {
                Model.UserSimplyInfo model = new Model.UserSimplyInfo();
                if (sdr.Read())
                {
                    model.Id = sdr.GetInt32(sdr.GetOrdinal("ID"));
                    model.Name = sdr.GetString(sdr.GetOrdinal("Name"));
                    model.Status = TypeParse.ObjectToInt(sdr.GetValue(sdr.GetOrdinal("Status")), 1);
                    model.Nature = sdr.GetInt32(sdr.GetOrdinal("Nature"));
                    model.Email = sdr.GetString(sdr.GetOrdinal("Email"));
                    model.Ip = sdr.GetString(sdr.GetOrdinal("Ip"));
                    model.CreateTime = sdr.GetDateTime(sdr.GetOrdinal("CreateTime"));
                    model.Password = sdr.GetString(sdr.GetOrdinal("Password"));
                    model.QQ = sdr.GetString(sdr.GetOrdinal("QQ"));
                    model.Flag = TypeParse.ObjectToInt(sdr.GetValue(sdr.GetOrdinal("Flag")), 0);
                    model.IsVip = TypeParse.ObjectToInt(sdr.GetValue(sdr.GetOrdinal("IsVip")), 0);
                    model.EndDate = sdr.GetDateTime(sdr.GetOrdinal("EndDate"));
                    model.IsOpen = TypeParse.ObjectToInt(sdr.GetValue(sdr.GetOrdinal("IsOpen")), 0);
                    sdr.Close();
                    return model;
                }
            }
            sdr.Close();
            return null;
        }
        #endregion

        #region 根据主键ID获取某一字段
        /// <summary>
        /// 根据主键ID获取某一字段
        /// </summary>
        /// <param name="id">主键id</param>
        /// <param name="fieldName">字段</param>
        public object GetOneFieldValueById(int id, string fieldName)
        {
            if (string.IsNullOrEmpty(fieldName))
            {
                fieldName = "0";
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Top 1 " + fieldName + " FROM User_SimplyInfo  ");
            strSql.Append("WHERE Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            return SqlHelper.GetSingle(Config.WebConfig.SqlConnString, strSql.ToString(), parameters);
        }
        #endregion

        #region 根据条件更新多个字段
        /// <summary>
        /// 根据条件更新多个字段
        /// </summary>
        /// <param name="cols">字段列表 以,分隔</param>
        /// <param name="values">字段值列表 以,分隔;与字段对应</param>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public int UpdateFields(string[] cols, object[] values, string where)
        {
            SqlParameter[] ps = new SqlParameter[values.Length];
            for (int i = 0; i < values.Length; i++)
            {
                ps[i] = new SqlParameter(cols[i], values[i] ?? DBNull.Value);
            }
            string[] ufs = new string[cols.Length];
            for (int i = 0; i < cols.Length; i++)
            {
                ufs[i] = cols[i] + "=@" + cols[i];
            }
            string uf = string.Join(",", ufs);
            string sql = string.Format("update User_SimplyInfo  set {0} where {1} ", uf, where);
            return Common.SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.Text, sql, ps);
        }
        #endregion

        #region 修改密码
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="OldPwd"></param>
        /// <param name="NewPwd"></param>
        /// <returns>0:旧密码不正确 1:成功 -1:失败</returns>
        public int UpdatePassword(int UserId, string OldPwd, string NewPwd)
        {
            SqlParameter returnParam = new SqlParameter("@returnValue", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;
            SqlHelper.ExecuteNonQuery(
                WebConfig.SqlConnString,
                CommandType.StoredProcedure,
                "User_SimplyInfo_UpdatePassword",
                new SqlParameter("@UserId", UserId),
                new SqlParameter("@OldPwd", OldPwd),
                new SqlParameter("@NewPwd", NewPwd),
                returnParam);
            return TypeParse.ObjectToInt(returnParam.Value, -1);
        }
        #endregion


        #region 根据用户名查找用户数
        /// <summary>
        /// 根据用户名查找用户数
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public int CheckUserExist(string Name)
        {
            return Common.TypeParse.ObjectToInt(Common.SqlHelper.ExecuteScalar(Config.WebConfig.SqlConnString,
                CommandType.StoredProcedure,
                "User_SimplyInfo_CheckUserNameExist",
                new SqlParameter("@Name", Name)), 0);
        }
        #endregion


        #region 根据邮箱是否已经使用
        /// <summary>
        /// 根据邮箱是否已经使用
        /// </summary>
        /// <param name="Email"></param>
        /// <returns></returns>
        public int CheckEmailExist(string Email)
        {
            return Common.TypeParse.ObjectToInt(SqlHelper.ExecuteScalar(Config.WebConfig.SqlConnString,
                CommandType.StoredProcedure,
                "User_SimplyInfo_CheckUserEmailExist",
                new SqlParameter("@Email", Email)), 0);
        }
        #endregion

        #region 登录验证
        /// <summary>
        /// 登录验证
        /// </summary>
        /// <param name="Name">帐号</param>
        /// <param name="Password">密码</param>
        /// <returns></returns>
        public Model.UserSimplyInfo CheckLogin(string Name, string Password)
        {
            SqlParameter[] parameters = { 
                                            new SqlParameter("@Name",Name),
                                            new SqlParameter("@Password",Password)
                                        };
            SqlDataReader sdr = SqlHelper.ExecuteReader(Config.WebConfig.SqlConnString,
                CommandType.StoredProcedure,
                "User_CheckLogin",
                parameters);
            if (sdr.HasRows)
            {
                Model.UserSimplyInfo model = new Model.UserSimplyInfo();
                if (sdr.Read())
                {
                    model.Id = sdr.GetInt32(sdr.GetOrdinal("Id"));
                    model.Name = sdr.GetString(sdr.GetOrdinal("Name"));
                    model.Status = TypeParse.ObjectToInt(sdr.GetValue(sdr.GetOrdinal("Status")), 1);
                    model.Nature = sdr.GetInt32(sdr.GetOrdinal("Nature"));
                    model.Email = sdr.GetString(sdr.GetOrdinal("Email"));
                    model.CreateTime = sdr.GetDateTime(sdr.GetOrdinal("CreateTime"));
                    sdr.Close();
                    return model;
                }
            }
            sdr.Close();
            return null;
        }
        #endregion


        #region 当天是否存在该IP
        /// <summary>
        /// 当天是否存在该IP
        /// </summary>
        /// <param name="ip"></param>
        /// <returns></returns>
        public bool IfExistIpToday(string ip)
        {
            string sql = "select * from User_SimplyInfo where Ip = @Ip and CreateTime > convert(char(10),getdate(),120)";
            SqlParameter parameter = new SqlParameter("@Ip", SqlDbType.NVarChar, 50);
            parameter.Value = ip;
            int row = Common.TypeParse.ObjectToInt(SqlHelper.ExecuteScalar(Config.WebConfig.SqlConnString, CommandType.Text, sql, parameter), 0);
            return row > 0;
        }
        #endregion
    }
}
